﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Hotnet.Dal.BaseRepository;
using Hotnet.Entity;
using Hotnet.Entity.Search;
using Hotnet.Entity.Enum;
using Hotnet.Entity.View;
using Hotnet.Dal.Utility;

namespace Hotnet.Dal.NSDalVideo
{
	/// <summary>
	/// 按照条件搜索video
	/// </summary>
	public class DalVideo : Repository<Video>
	{
		/// <summary>
		/// 根据条件,查询video
		/// </summary>
		/// <param name="pageNo"></param>
		/// <param name="pageSize"></param>
		/// <param name="totalCount"></param>
		/// <param name="searchObj"></param>
		/// <returns></returns>
		public ViewListVideo[] List(int pageNo, int pageSize, out int totalCount, SearchVideoCondition searchObj,int? isPublished)
		{
			string videoOrderByCondtion = "";
			string videoNameCondition = "";
			string videoSourceFromCondition = "";
			string videoDefineTypeCondition = "";
			string videoIsGroupCondition = "";
			string videoCategoryCondition = "";
			string videoDayBeforeCondition = "";
			string isPublishedCondition = "";
			if (isPublished != null && isPublished != (int)EnumPublishState.全部)
				isPublishedCondition = string.Format(" and ispublished={0} ", isPublished.Value);

			int from = pageNo * pageSize + 1;
			int to = (pageNo + 1) * pageSize;

			if (!string.IsNullOrEmpty(searchObj.VideoName))
				videoNameCondition = string.Format(" and Video.Name like '%{0}%' ", searchObj.VideoName);
			if (searchObj.DefineType != null && searchObj.DefineType!=-1)
				videoDefineTypeCondition = string.Format(" and DefineType ={0} ", searchObj.DefineType.Value);
			if (searchObj.SourceFromID!=null && searchObj.SourceFromID!=-1)
				videoSourceFromCondition = string.Format(" and Video.SourceFromID={0} ", searchObj.SourceFromID);
			if(searchObj.IsGroup!=null)
				videoIsGroupCondition = string.Format(" and ({0})", searchObj.IsGroup.Value ? "Video.IsGroup=1" : "Video.IsGroup=0 or Video.IsGroup is null");
			if (searchObj.OrderType==null||searchObj.OrderType == (int)EnumOrder.添加时间)
				videoOrderByCondtion = " Video.AddTime ";
			else if(searchObj.OrderType==(int)EnumOrder.点击次数)
				videoOrderByCondtion = "ClickCount ";
			if (searchObj.CategoryID!=null&&searchObj.CategoryID.Value != 0)
				videoCategoryCondition = string.Format(" and Video.CategoryID={0} ",searchObj.CategoryID);
			if (searchObj.DayBefore != null)
				videoDayBeforeCondition = string.Format(" and Video.AddTime>='{0}' ", DateTime.Today.AddDays(-searchObj.DayBefore.Value));

			string sql = string.Format(@"
						Select * from (
						SELECT	
							ROW_NUMBER() OVER (ORDER BY {0} desc)AS Row,
							VideoID,
							Video.Name,
							Description,
							VideoSrc,
							DownLoadUrl,
							IsDVD,
							IsCanPlayInner,
							IsGroup,
							Video.CategoryID,
							CoverImagePath,
							Director,
							Zone,
							PreviewTime, 
							Video.SourceFromID,
							Video.AddTime, 
							DefineType, 
							VideoCategory.Name AS VideoCategoryName, 
							SourceFrom.Name AS SourceFromName,
							ClickCount,
							IsPublished
							FROM	
								Video INNER JOIN
								VideoCategory ON Video.CategoryID = VideoCategory.CategoryID INNER JOIN
								SourceFrom ON Video.SourceFromID = SourceFrom.SourceFromID
								WHERE
									1=1 
									{6} 
									{1} 
									{2} 
									{3} 
									{7} 
									{8} 
									{9} 
									{10} 
							) as list
					WHERE Row between {4} and {5}", videoOrderByCondtion, videoNameCondition, videoDefineTypeCondition, videoSourceFromCondition, from, to,videoCategoryCondition,videoIsGroupCondition,videoDayBeforeCondition,videoIsGroupCondition,isPublishedCondition);
			string sqlCount = string.Format(@"
						SELECT COUNT(VideoID)
							FROM 
								Video INNER JOIN
								VideoCategory ON Video.CategoryID = VideoCategory.CategoryID INNER JOIN
								SourceFrom ON Video.SourceFromID = SourceFrom.SourceFromID
								WHERE
									1=1 
									{3} 
									{0} 
									{1} 
									{2} 
									{4} 
									{5} 
									{6} 
								", videoNameCondition, videoDefineTypeCondition, videoSourceFromCondition,videoCategoryCondition,videoDayBeforeCondition,videoIsGroupCondition,isPublishedCondition);
			totalCount = disableCacheDataContext.ExecuteQuery<int>(sqlCount).FirstOrDefault();
			ViewListVideo[] list = disableCacheDataContext.ExecuteQuery<ViewListVideo>(sql).ToArray();

			return list;
		}


		/// <summary>
		/// 列出大网站前x个焦点视频
		/// </summary>
		/// <param name="topNumber"></param>
		/// <param name="defineType"></param>
		/// <returns></returns>
		public ViewVideoList[] ListTopXEveryNet(int topNumber, EnumVideoListType listType)
		{
			string strSql = string.Format(@"select t2.* from 
											(select SourceFromID from sourceFrom where NetType={1} and mark=1) t1
												cross apply
											(select top {0} 
										Video.VideoID, 
										Video.Name,
										Video.Description,
										Video.VideoSrc,
										Video.DownLoadUrl,
										Video.IsDVD,
										Video.IsCanPlayInner, 
										Video.CategoryID,
										Video.CoverImagePath,
										Video.Director,
										Video.Zone,
										Video.PreviewTime,
										Video.SourceFromID, 
										Video.AddTime,
										Video.DefineType,
										Video.ClickCount,
										Video.IsGroup,
										Video.IsPublished,
										VideoList.ListType, 
										VideoList.CoverImageSrc,
										VideoList.IsPublished AS VideoListIsPublished from video,videoList where video.videoID=videoList.videoID and videoList.listType={2} and SourceFromID=t1.sourceFromID and video.isPublished=1 and videolist.ispublished=1 order by AddTime desc) t2
										", topNumber, (int)EnumSystemCategory.视频, (int)listType);
			return disableCacheDataContext.ExecuteQuery<ViewVideoList>(strSql).ToArray();
		}

		/// <summary>
		/// 根据sourceFromId 和 网站定义列表 和 日期 列出video，不分页
		/// </summary>
		/// <param name="sourceFromIds"></param>
		/// <param name="defineType"></param>
		/// <param name="begin"></param>
		/// <param name="end"></param>
		/// <returns></returns>
		public Video[] ListVideoByNetAndDate(int[] sourceFromIds,EnumDefineType defineType,DateTime begin,DateTime end)
		{
			return givenTable.Where(x => sourceFromIds.Contains(x.SourceFromID.Value) && x.DefineType == (int)defineType && x.AddTime >= begin && x.AddTime <= end && x.IsPublished==1).ToArray();
		}

		/// <summary>
		/// 列出最新的视频
		/// </summary>
		/// <param name="number"></param>
		/// <returns></returns>
		public Video[] ListLatestVideo(int number)
		{
			return givenTable.Where(x=>(x.DefineType==null||x.DefineType==-1) && x.IsPublished==1).OrderByDescending(x=>x.AddTime).Take(number).ToArray();
		}

		/// <summary>
		/// 列出最热的视频
		/// </summary>
		/// <param name="number"></param>
		/// <returns></returns>
		public Video[] ListHotVideo(int number)
		{
			return givenTable.Where(x => (x.DefineType == null||x.DefineType==-1) && x.IsPublished==1 ).OrderByDescending(x => x.ClickCount).Take(number).ToArray();
		}

		/// <summary>
		/// 列出defineType
		/// </summary>
		/// <param name="number">数量</param>
		/// <returns></returns>
		public Video[] ListDefineTypeVideoes(int number,EnumDefineType defineType,EnumOrder order)
		{
			if(order== EnumOrder.添加时间)
				return disableCacheDataContext.Video.Where(x => x.DefineType == (int)defineType && x.IsPublished==1).OrderByDescending(x => x.AddTime).Take(number).ToArray();
			else
				return disableCacheDataContext.Video.Where(x => x.DefineType == (int)defineType && x.IsPublished==1).OrderByDescending(x => x.ClickCount).Take(number).ToArray();
		}

		/// <summary>
		/// 列出defineType,某一个分类
		/// </summary>
		/// <param name="number">数量</param>
		/// <returns></returns>
		public Video[] ListDefineTypeVideoes(int number, EnumDefineType defineType,int categoryID, EnumOrder order)
		{
			if (order == EnumOrder.添加时间)
				return disableCacheDataContext.Video.Where(x => x.DefineType == (int)defineType && x.CategoryID==categoryID && x.IsPublished==1).OrderByDescending(x => x.AddTime).Take(number).ToArray();
			else
				return disableCacheDataContext.Video.Where(x => x.DefineType == (int)defineType && x.CategoryID == categoryID && x.IsPublished==1).OrderByDescending(x => x.ClickCount).Take(number).ToArray();
		}

		/// <summary>
		/// 列出某个分类 defineType的视频
		/// </summary>
		/// <param name="categoryId"></param>
		/// <param name="number"></param>
		/// <param name="defineType"></param>
		/// <param name="order"></param>
		/// <returns></returns>
		public Video[] ListDefineTypeVideos(int categoryId,int number, EnumDefineType defineType, EnumOrder order)
		{
			if (order == EnumOrder.添加时间)
				return disableCacheDataContext.Video.Where(x => x.DefineType == (int)defineType && x.CategoryID==categoryId&&x.IsPublished==1).OrderByDescending(x => x.AddTime).Take(number).ToArray();
			else
				return disableCacheDataContext.Video.Where(x => x.DefineType == (int)defineType && x.CategoryID == categoryId&&x.IsPublished==1).OrderByDescending(x => x.ClickCount).Take(number).ToArray();
		}

		/// <summary>
		/// 列出历史点击次数的视频,近一天,近一周,近一月...
		/// </summary>
		/// <param name="topNumber"></param>
		/// <returns></returns>
		public VideoHistoryHot[] ListVideoHistoryHot(int topNumber)
		{
			StringBuilder sb = new StringBuilder();
			List<int> spans=Enum.GetValues(typeof(EnumVideoHistoryHotTimeSpan)).Cast<int>().ToList();
			for (int i = 0; i < spans.Count(); i++)
			{
				if (i == 0)
				{
					sb.AppendFormat(" select {0} as VideoTimeSpan ", spans[i]);
					continue;
				}
				sb.AppendFormat(" union select {0} ", spans[i]);
			}
			string strSql = string.Format(@"select * from 
								(
									{0}
								) t1
								cross apply
								(	
									select top {1} * from video 
									where addTime>(Dateadd(day,-t1.VideoTimeSpan,getdate())) and isPublished=1 order by clickCount desc 
								)t2", sb.ToString(), topNumber);
			return disableCacheDataContext.ExecuteQuery<VideoHistoryHot>(strSql).ToArray();
		}

		/// <summary>
		/// 列出视频分类点击靠前的视频,
		/// </summary>
		/// <param name="topNumber">top 数</param>
		/// <returns></returns>
		public Video[] ListTopXVideoByCategory(int topNumber)
		{
			//TODO 此DalVideo 视频得考虑重复问题,就是不在定义的define列表中
			string strSql = string.Format(@"select t2.* from videoCategory t1
										cross apply
										(
											select top {0} * from video where video.categoryID=t1.categoryID and isPublished=1 order by AddTime desc
										) t2", topNumber);
			return disableCacheDataContext.ExecuteQuery<Video>(strSql).ToArray();
		}

		/// <summary>
		/// 查找相关视频或者视频组。
		/// 此处与下面 ListVideoByTag函数基本相似,但是此函数根据tagID关联,所以性能上高些,待测试,是否要把此函数替换,TODO
		/// </summary>
		/// <param name="tagIds"></param>
		/// <returns></returns>
		public Video[] ListTopXVideoByTags(int[] tagIds,int number)
		{
			//TODO 去除本身
			var queryVideos = (from refVideoTag in disableCacheDataContext.VideoXTag
							  where tagIds.Contains(refVideoTag.TagID)
							  join refVideo in disableCacheDataContext.Video on refVideoTag.VideoID equals refVideo.VideoID
							  where refVideo.IsPublished==1
							  orderby refVideo.ClickCount descending
							  select refVideo).Distinct().Take(number);
			Video[] videos = queryVideos.ToArray();
			return videos;
		}

		public Video[] VideoSupplyWhenNotEnough(Video[] videos,int number)
		{
			//不足补充
			int notEnough=number - videos.Count();
			if ( notEnough> 0)
			{
				Video[] tempVideos = videos;
				var queryNotEnough = (from video in diableCacheGiveTable where !tempVideos.Select(x => x.VideoID).Contains(video.VideoID)&& video.IsPublished==1 select video).OrderByDescending(x => disableCacheDataContext.GetNewId()).Take(notEnough);
				var enoughVidoes = queryNotEnough.ToArray();
				videos=videos.Union(enoughVidoes).ToArray();
			}
			return videos;
		}

		/// <summary>
		/// 按照类型和网站来源列出本月的视频每日的数量
		/// </summary>
		/// <param name="sourceFromId"></param>
		/// <returns></returns>
		public VideoDayCount[] ListVideoCountByMonth(int[] sourceFromId, EnumDefineType defineType,DateTime dayInMonth)
		{
			DateTime firstDayOfMonth = dayInMonth.AddDays(-dayInMonth.Day+1);
			string sql = string.Format(@"select count(videoID) as Count,CONVERT(VARCHAR(10) ,addTime, 111) as VideoDate from video
										where AddTime>='{0}' and AddTime<='{1}' and DefineType={2} and sourceFromId in {3} and isPublished=1
										group by CONVERT(VARCHAR(10), addTime, 111)", firstDayOfMonth.ToString("yyyy/MM/dd"), dayInMonth.AddMonths(1).ToString("yyyy/MM/dd"), (int)defineType,SqlUility.ConvertArrayToInStatement<int>(sourceFromId));
			return disableCacheDataContext.ExecuteQuery<VideoDayCount>(sql).ToArray();
		}

		/// <summary>
		/// 根据TagName 查询 video,tag可以为多个,中间用空格分隔
		/// </summary>
		/// <param name="tagNames"></param>
		/// <returns></returns>
		public Video[] ListVideoByTag(int pageNo, int pageSize, out int totalCount, SearchByTagConditon searchObj)
		{
			int from = pageNo * pageSize + 1;
			int to = (pageNo + 1) * pageSize;
			StringBuilder where = new StringBuilder();
			string[] tagNames = searchObj.TagName.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
			for (int i = 0; i < tagNames.Count(); i++)
			{
				tagNames[i] = SqlUility.GetQueryStringSafe(tagNames[i]);
				if (i != tagNames.Count() - 1)
					where.AppendFormat(" Tag.tagName like '%{0}%' or Video.Name like '%{0}%' or ", tagNames[i]);
				else
					where.AppendFormat(" Tag.tagName like '%{0}%' or Video.Name like '%{0}%' ", tagNames[i]);
			}
			string strSql = string.Format(
					@"
					Select * from (
					Select list.*,ROW_NUMBER() OVER (ORDER BY list.ClickCount desc)AS Row
					from (
						SELECT	
							  distinct
							   Video.[VideoID]
							  ,[Name]
							  ,[Description]
							  ,[VideoSrc]
							  ,[DownLoadUrl]
							  ,[IsDVD]
							  ,[IsCanPlayInner]
							  ,[CategoryID]
							  ,[CoverImagePath]
							  ,[Director]
							  ,[Zone]
							  ,[PreviewTime]
							  ,[SourceFromID]
							  ,Video.[AddTime]
							  ,[DefineType]
							  ,Video.[ClickCount]
							  ,[IsGroup]
							FROM	
								Video INNER JOIN
								VideoXTag ON Video.VideoID = videoXTag.VideoID INNER JOIN
								Tag ON videoXTag.TagID = Tag.TagID
								where isPublished=1 and ({0})
					) as list) as result
					WHERE Row between {1} and {2}",where.ToString(),from,to
				);
			string strSqlCount = string.Format(
						@"Select count(*) as count
						from (
						SELECT	
							distinct
							Video.[VideoID]
							FROM	
								Video INNER JOIN
								VideoXTag ON Video.VideoID = videoXTag.VideoID INNER JOIN
								Tag ON videoXTag.TagID = Tag.TagID
								where isPublished=1 and ({0})
					) as list
						", where.ToString(), from, to
					);
			totalCount = disableCacheDataContext.ExecuteQuery<int>(strSqlCount).First();
			Video[] videos = disableCacheDataContext.ExecuteQuery<Video>(strSql).ToArray();

			return videos;
		}

	}
}
